<?php

/**
 * Compare the schemas of between databases.
 * 
 * For two database schemas to be considered the same, they must have the same
 * tables, where each table has the same fields, and each field has the same
 * parameters.
 * 
 * Field parameters that are compared are those that are given by the MySQL
 * 'SHOW COLUMNS' command. These are: the field's name, it's type, whether the
 * field can store null values, whether the column is indexed, the default
 * values and whether the field was created with the 'auto_increment' keyword.
 * 
 * More information on this tool can be found at:
 * http://joefreeman.co.uk/blog/2009/07/php-script-to-compare-mysql-database-schemas/
 * 
 * Copyright (C) 2009, Joe Freeman <joe.freeman@bitroot.com>
 * Available under http://en.wikipedia.org/wiki/MIT_License
 * 
 * @package default
 * @author Joe Freeman
 */
class DbDiff {
        public static $schema1_name;
        public static $schema2_name;
              
        
	/**
	 * Export the schema of the database into an array.
	 *
	 * @param string $config Config details for the database connection.
	 * @param string $name Name or description of the database.
	 * @return mixed|string An array structure of the exported schema, or an error string.
	 */
	function export($config, $name) {
		
		$db = @mysql_connect($config['host'], $config['user'],
			$config['password']);
		
		if (!$db) {
			return null;
		}
		
		if (!mysql_select_db($config['name'], $db)) {
			return null;
		}
		
		$result = mysql_query("SHOW TABLES");
		while ($row = mysql_fetch_row($result)) {
			$tables[$row[0]] = array();
		}
		
		foreach ($tables as $table_name => $fields) {
			
			$result = mysql_query("SHOW COLUMNS FROM " . $table_name, $db);
                        if(!$result)
                            echo "<br>WARNING: View or Table has no columns '$table_name' ".$result;
                        else
                            while ($row = mysql_fetch_assoc($result)) {
                                    $tables[$table_name][$row['Field']] = $row;
                            }
		}
		
		mysql_close();
		
		$data = array(
			'name' => $name,
			'time' => time(),
			'tables' => $tables
		);
		
		return $data;
	}
	
	/**
	 * Compare two schemas (as generated by the 'export' method.)
	 *
	 * @param string $schema1 The first database schema.
	 * @param string $schema2 The second database schema.
         * @param string $change_1to2 [output] SQL to make Schema 1 to become Schema 2.
	 * @param string $change_2to1 [output] SQL to make Schema 2 to become Schema 1.
         * 
	 * @return void
	 */
	function compare($schema1, $schema2,&$change_1to2,&$change_2to1) {
		
		$tables1 = array_keys($schema1['tables']);
		$tables2 = array_keys($schema2['tables']);
                self::$schema1_name = $schema1['name'];
		self::$schema2_name = $schema2['name'];
		
		$tables = array_unique(array_merge($tables1, $tables2));
		
		$results = array();
		$change_1to2 = array();
                $change_2to1 = array();
                
		foreach ($tables as $table_name) {
			
                        $alter_table = " ALTER TABLE `$table_name` ";
                    
			// Check tables exist in both databases
			
			if (!isset($schema1['tables'][$table_name])) {
				
				$results[$table_name][] = '<em>' . $schema1['name']
					. '</em> is missing table: <code>' . $table_name
					. '</code>.';
				
				continue;
			}
			
			if (!isset($schema2['tables'][$table_name])) {
				
				$results[$table_name][] = '<em>' . $schema2['name']
					. '</em> is missing table: <code>' . $table_name
					. '</code>.';
				
				continue;
			}
			
			// Check fields exist in both tables
			
			$fields = array_merge($schema1['tables'][$table_name],
				$schema2['tables'][$table_name]);
			
			foreach ($fields as $field_name => $field) {
                                
                                $add_line = $alter_table; 
                                $drop_line = $alter_table ." DROP COLUMN `$field_name`; ";
                            
				if (!isset($schema1['tables'][$table_name][$field_name])) {
					
					$results[$table_name][] = '<em>' . $schema1['name']
						. '</em> is missing field: <code>' . $field_name
						. '</code>';
					$change_1to2[$table_name][]=$drop_line."/* run on ".self::$schema2_name."  */";
					$change_2to1[$table_name][]=$add_line
                                            .self::writeAddLineNewColumn($schema2['tables'][$table_name][$field_name],$field_name).';'
                                            ." /* run on ".self::$schema1_name."  */"                                                ;
                                        continue;
				}
				
				if (!isset($schema2['tables'][$table_name][$field_name])) {
					
					$results[$table_name][] = '<em>' . $schema2['name']
						. '</em> is missing field: <code>' . $field_name
						. '</code>';
					$change_2to1[$table_name][]=$drop_line."/* run on ".self::$schema1_name."  */";;
					$change_1to2[$table_name][]=$add_line
                                            .self::writeAddLineNewColumn($schema1['tables'][$table_name][$field_name],$field_name).';'."/* run on ".self::$schema2_name."  */";					
                                        continue;
				}
				
				// Check that the specific parameters of the fields match
				
				$s1_params = $schema1['tables'][$table_name][$field_name];
				$s2_params = $schema2['tables'][$table_name][$field_name];
				
                                $diff_on_parameters=false;
                                
				foreach ($s1_params as $name => $details) {
					if (self::value_key_if_exists($s1_params,$name) != self::value_key_if_exists($s2_params,$name)) {
						$results[$table_name][] = 'Field <code>' . $field_name
							. "</code> differs for parameter '$name' </br>  " 
                                                        . ' ----<em>' . self::$schema1_name
							. '</em> has \'' . self::value_key_if_exists($s1_params,$name).'</br>'
							. ' ----<em>' . self::$schema2_name
							. '</em> has \'' . self::value_key_if_exists($s2_params,$name) 
                                                        . '\'.';
                                                
                                            $diff_on_parameters = true; /** it would be better just to count $results[..] 
                                            * but it is not visible what is from missing fields or from missing parameters on same field
                                            */
                                        }// end if
				}// end looping over the parameters
                                if($diff_on_parameters){
                                    $change_2to1[$table_name][]=$alter_table.self::writeAddLineAlterColumn($s1_params, $field_name)."/* run on ".self::$schema2_name."  */";
                                    $change_1to2[$table_name][]=$alter_table.self::writeAddLineAlterColumn($s2_params, $field_name)."/* run on ".self::$schema1_name."  */";
                                }
			}
		}
		
		return $results;
	}
        
        function writeColumnDefinition($params,$field_name){
            $o = null;
            if(is_array($params))
                {$o=(object)$params;}
            else if(is_object($params)){$o=$params;}
            else{error("Unknown TYpe ".gettype($params));
                return;
            }
            /** column_definition: from mySQL  http://dev.mysql.com/doc/refman/5.0/en/create-table.html
       data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string'] [reference_definition]*/
            /*Field created parameter 'Field'. has 'created
Field created parameter 'Type'. has 'datetime
Field created parameter 'Null'. has 'YES
Field created parameter 'Key'. has '
Field created parameter 'Default'. has '
Field created parameter 'Extra'. has '*/
            $null = 'NOT NULL' ;//. "/* o->Null = $o->Null */";
            if($o->Null=='YES') 
                $null = 'NULL';
            $default = '';
            if($o->Default) $default = ' DEFAULT '.$o->Default;
            $key = '';
            $extras = '';
            return " $o->Type $null $default $key $extras ";
        }// end writeColumnDefinition

        function writeAddLineNewColumn($params,$field_name){
            //change array to object
            //$res = 'Write Lines';
            //SQL is anything after Alter Table
            return " ADD COLUMN `$field_name` ".self::writeColumnDefinition($params,$field_name);
            
            /*foreach ($params as $name => $details) {
					//if ($s1_params[$name] != $s2_params[$name]) 
                                            {
						$res.= '<br/>Field <code>' . $field_name
							. '</code> parameter \''
							. $name . '\'. <em>' .self::$schema1_name
							. '</em> has \'' . $params[$name]
							;
					}//end if
				}// end foreach
             */
        }// end writeAddLinewNewColumn    
        
        static function writeAddLineAlterColumn($params,$field_name){
            //change array to object
            //$res = 'Write Lines';
            //SQL is anything after Alter Table
            return " ALTER COLUMN `$field_name` ".self::writeColumnDefinition($params,$field_name);
            
            /*foreach ($params as $name => $details) {
					//if ($s1_params[$name] != $s2_params[$name]) 
                                            {
						$res.= '<br/>Field <code>' . $field_name
							. '</code> parameter \''
							. $name . '\'. <em>' .self::$schema1_name
							. '</em> has \'' . $params[$name]
							;
					}//end if
				}// end foreach
             */
        }// end writeAddLinewNewColumn 
        
        static function value_key_if_exists($array,$key){
            if(array_key_exists($key, $array))
                    return $array[$key];
            return '(BLANK)';            
        }
        
}